import logging
from datetime import date
from typing import Optional, List

from fastapi import APIRouter, Query, Depends
from pydantic import BaseModel, Field
from sqlalchemy import text
from sqlalchemy.exc import SQLAlchemyError
from sqlalchemy.orm import Session

from commons.BaseResponse import BaseResponse
from commons.PaginationBase import PaginationBase
from datebase import get_db
from utils.EncryptionUtil import oauth2_scheme, EncryptionMiddleware

order_head_router = APIRouter(prefix="/purchase/ordHead", tags=["采购申请管理"], dependencies=[Depends(oauth2_scheme)])


class OrdSearch(PaginationBase):
    fsiteNo: str = Field(None, description="客户编号")
    ftakedateStart: str = Field(None, description="开始时间")
    ftakedateEnd: str = Field(None, description="结束时间")
    fsupno: str = Field(None, description="供应商编号")
    fbillno: str = Field(None, description="单号")
    fplanuser: str = Field(None, description="")
    forduser: str = Field(None, description="采购人员")
    fbillstate: str = Field(None, description="单据状态(1:待提交/2:已提交/8:已作废)")


@order_head_router.get("/list", summary="查询采购信息")
def list_order_head(ord: OrdSearch = Query(), db: Session = Depends(get_db)):
    param = {}
    sql = "select count(1) from lims.s_tc_ord_head where 1=1"
    if ord.fsiteNo is not None:
        sql = sql + " and fsite_no = :site_no "
        param["site_no"] = ord.fsiteNo
    if ord.fsupno is not None:
        sql = sql + " and fsupno like concat('%', :fsupno ,'%')"
        param["fsupno"] = ord.fsupno
    if ord.fbillno is not None:
        sql = sql + " and fbillno like concat('%', :fbillno ,'%')"
        param["fbillno"] = ord.fbillno
    if ord.fplanuser is not None:
        sql = sql + " and fplanuser = :fplanuser "
        param["fplanuser"] = ord.fplanuser
    if ord.fbillstate is not None:
        sql = sql + " and fbillstate = :fbillstate "
        param["fbillstate"] = ord.fbillstate
    if ord.forduser is not None:
        sql = sql + " and forduser = :forduser "
        param["forduser"] = ord.forduser
    if (ord.ftakedateStart is not None) and (ord.ftakedateEnd is not None):
        sql = sql + " and to_char(forddate,'yyyymmdd') between  :ftakedateStart and  :ftakedateEnd "
        param["ftakedateStart"] = ord.ftakedateStart
        param["ftakedateEnd"] = ord.ftakedateEnd

    count = db.execute(text(sql), param).scalar()

    param = {}
    sql = "select fbillno,fsite_no,forduser,forddate,fplanrecdate,fsupno,fitemcount,fpiececount,"
    sql = sql + " ftotalamt,fsrcbillno,fexplanation,fupddate,foperator,fcheckdate,fcheckuser,fbillstate,fbilltype"
    sql = sql + " from lims.s_tc_ord_head where 1=1"
    if ord.fsiteNo is not None:
        sql = sql + " and fsite_no = :site_no "
        param["site_no"] = ord.fsiteNo
    if ord.fsupno is not None:
        sql = sql + " and fsupno like concat('%', :fsupno ,'%')"
        param["fsupno"] = ord.fsupno
    if ord.fbillno is not None:
        sql = sql + " and fbillno like concat('%', :fbillno ,'%')"
        param["fbillno"] = ord.fbillno
    if ord.fplanuser is not None:
        sql = sql + " and fplanuser = :fplanuser "
        param["fplanuser"] = ord.fplanuser
    if ord.fbillstate is not None:
        sql = sql + " and fbillstate = :fbillstate "
        param["fbillstate"] = ord.fbillstate
    if ord.forduser is not None:
        sql = sql + " and forduser = :forduser "
        param["forduser"] = ord.forduser
    if (ord.ftakedateStart is not None) and (ord.ftakedateEnd is not None):
        sql = sql + " and to_char(forddate,'yyyymmdd') between  :ftakedateStart and  :ftakedateEnd "
        param["ftakedateStart"] = ord.ftakedateStart
        param["ftakedateEnd"] = ord.ftakedateEnd
    if ord.order == "desc":
        sql = sql + " order by  forddate,fbillno desc "
    else:
        sql = sql + " order by  forddate,fbillno "
    sql = sql + " limit :limit offset :offset "
    param["offset"] = ord.offset
    param["limit"] = ord.limit

    data = db.execute(text(sql), param).fetchall()

    # details 明细列表
    param = {}
    sql_3 = " select b.fbillno,b.fline,b.feuipmentid,b.fbuyprice,b.fqty,b.ftotamt,b.fmemo"
    sql_3 = sql_3 + " from lims.s_tc_ord_head a,lims.s_tc_ord_detail b where a.fbillno=b.fbillno "
    if ord.fsiteNo is not None:
        sql_3 = sql_3 + " and fsite_no = :site_no "
        param["site_no"] = ord.fsiteNo
    if ord.fsupno is not None:
        sql_3 = sql_3 + " and fsupno like concat('%', :fsupno ,'%')"
        param["fsupno"] = ord.fsupno
    if ord.fbillno is not None:
        sql_3 = sql_3 + " and a.fbillno like concat('%', :fbillno ,'%')"
        param["fbillno"] = ord.fbillno
    if ord.fplanuser is not None:
        sql_3 = sql_3 + " and fplanuser = :fplanuser "
        param["fplanuser"] = ord.fplanuser
    if ord.fbillstate is not None:
        sql_3 = sql_3 + " and fbillstate = :fbillstate "
        param["fbillstate"] = ord.fbillstate
    if (ord.ftakedateStart is not None) and (ord.ftakedateEnd is not None):
        sql_3 = sql_3 + " and to_char(forddate,'yyyymmdd') between :ftakedateStart and :ftakedateEnd "
        param["ftakedateStart"] = ord.ftakedateStart
        param["ftakedateEnd"] = ord.ftakedateEnd
    sql_3 = sql_3 + " order by b.fbillno,b.fline"

    data_detail = db.execute(text(sql_3), param).fetchall()

    data_list = []
    for key in range(len(data)):
        d1 = {}
        d1['fbillno'] = data[key][0]
        d1['fsiteno'] = data[key][1]
        d1['forduser'] = data[key][2]
        d1['forddate'] = data[key][3]
        d1['fplanrecdate'] = data[key][4]
        d1['fsupno'] = data[key][5]
        d1['fitemcount'] = data[key][6]
        d1['fpiececount'] = data[key][7]
        d1['ftotalamt'] = data[key][8]
        d1['fsrcbillno'] = data[key][9]
        d1['fexplanation'] = data[key][10]
        d1['fupddate'] = data[key][11]
        d1['foperator'] = data[key][12]
        d1['fcheckdate'] = data[key][13]
        d1['fcheckuser'] = data[key][14]
        d1['fbillstate'] = data[key][15]
        d1['fbilltype'] = data[key][16]
        sbillno = data[key][0]

        detail_list = []
        for j1 in range(len(data_detail)):
            if (str(data_detail[j1][0]) == str(sbillno)):
                d2 = {}
                d2['fbillno'] = data_detail[j1][0]
                d2['fline'] = data_detail[j1][1]
                d2['feuipmentid'] = data_detail[j1][2]
                d2['fbuyprice'] = float(data_detail[j1][3])
                d2['fqty'] = data_detail[j1][4]
                d2['ftotamt'] = data_detail[j1][5]
                d2['fmemo'] = data_detail[j1][6]
                detail_list.append(d2)

        d1["stcOrdDetails"] = detail_list
        data_list.append(d1)

    info_dict = {"list": data_list, "limit": ord.limit, "page": ord.pageIndex,
                 "pages": (count + ord.limit - 1) // ord.limit,
                 "total": count}
    return BaseResponse.success(data=info_dict)


class StcOrdDetailCreate(BaseModel):
    """设备采购申请明细创建模型"""
    fline: int = Field(..., description="行号")
    feuipmentid: str = Field(..., description="设备ID")
    fbuyprice: float = Field(..., description="采购单价")
    fqty: float = Field(..., description="采购数量")
    ftotamt: float = Field(..., description="总金额")
    fmemo: Optional[str] = Field("", description="备注，默认为空字符串")


class StcOrdHeaderCreate(BaseModel):
    """设备采购申请表头创建模型"""
    fbillno: str = Field(..., description="申请单号")
    fsiteNo: str = Field(..., description="站点编号")
    forduser: str = Field(..., description="申请人")
    forddate: date = Field(..., description="申请日期")  # 若实际为字符串，可改为str类型
    fplanrecdate: date = Field(..., description="计划到货日期")  # 同上
    fupddate: date = Field(..., description="更新日期")  # 同上
    fsupno: str = Field(..., description="供应商编号")
    fexplanation: Optional[str] = Field("", description="申请说明，默认为空字符串")
    stcOrdDetails: List[StcOrdDetailCreate] = Field(..., description="采购明细列表")
    fbillstate: int = Field(1, description="单据状态，默认值为1")  # 代码中默认传1


@order_head_router.post("/create", summary="新增设备采购信息")
def create_order_head(stc: StcOrdHeaderCreate, db: Session = Depends(get_db)):
    try:
        fitemcount = 0
        fpiececount = 0
        ftotalamt = 0
        fbillno = stc.fbillno
        # 返回内容
        d1 = {}
        detail_list = []
        with db.begin():
            jsondata = stc.stcOrdDetails
            for data in jsondata:
                sql = "insert into lims.s_tc_ord_detail(fbillno,fline,feuipmentid,fbuyprice,fqty,ftotamt,fmemo)"
                sql = sql + " values( :fbillno , :fline , :feuipmentid , :fbuyprice , :fqty , :ftotamt , :fmemo )"
                db.execute(text(sql), {
                    "fbillno": fbillno,
                    "fline": data.fline,
                    "feuipmentid": data.feuipmentid,
                    "fbuyprice": data.fbuyprice,
                    "fqty": data.fqty,
                    "ftotamt": data.ftotamt,
                    "fmemo": data.fmemo
                })

                fitemcount = fitemcount + 1
                fpiececount = fpiececount + data.fqty
                ftotalamt = ftotalamt + data.ftotamt

                # 返回明细
                d2 = {}
                d2['fbillno'] = fbillno
                d2['fline'] = data.fline
                d2['feuipmentid'] = data.feuipmentid
                d2['fbuyprice'] = float(data.fbuyprice)
                d2['fqty'] = float(data.fqty)
                d2['ftotamt'] = float(data.ftotamt)
                d2['fmemo'] = data.fmemo
                detail_list.append(d2)
            sql = "insert into lims.s_tc_ord_head(fbillno,fsite_no,forduser,fplanrecdate,forddate,fsupno,fitemcount,fpiececount,ftotalamt,fexplanation,fupddate,foperator,fbilltype,fbillstate)"
            sql = sql + " values( :fbillno , :fsiteNo , :forduser , :fplanrecdate , :forddate , :fsupno , :fitemcount , :fpiececount , :ftotalamt , :fexplanation , :fupddate , :forduser ,1, :fbillstate )"
            db.execute(text(sql), {
                "fbillno": fbillno,
                "fsiteNo": stc.fsiteNo,
                "forduser": stc.forduser,
                "fplanrecdate": stc.fplanrecdate,
                "forddate": stc.forddate,
                "fsupno": stc.fsupno,
                "fitemcount": fitemcount,
                "fpiececount": fpiececount,
                "ftotalamt": ftotalamt,
                "fexplanation": stc.fexplanation,
                "fupddate": stc.fupddate,
                "fbillstate": stc.fbillstate
            })

            # 返回
            d1['fbillno'] = fbillno
            d1['fsiteno'] = stc.fsiteNo
            d1['forddate'] = stc.forddate
            d1['fplanrecdate'] = stc.fplanrecdate
            d1['forduser'] = stc.forduser
            d1['fsupno'] = stc.fsupno
            d1['fitemcount'] = fitemcount
            d1['fpiececount'] = fpiececount
            d1['ftotalamt'] = float(ftotalamt)
            d1['ftakedateEnd'] = None
            d1['ftakedateStart'] = None
            d1['fsrcbillno'] = ""
            d1['fexplanation'] = stc.fexplanation
            d1['fupddate'] = stc.fupddate
            d1['foperator'] = stc.forduser
            d1['fcheckdate'] = None
            d1['fcheckuser'] = None
            d1['fbilltype'] = 1
            d1['fbillstate'] = 1
            d1["stcOrdDetails"] = detail_list
            return BaseResponse.success(data=d1)
    except SQLAlchemyError as e:
        # 发生异常时自动回滚
        db.rollback()
        # 记录错误日志
        logging.error(f"数据库错误: {str(e)}")
        # 返回错误响应
        return BaseResponse.error(message="数据库操作失败")
    except Exception as e:
        db.rollback()
        logging.error(f"未知错误: {str(e)}")
        return BaseResponse.error(message="令牌过期")


@order_head_router.post("/update", summary="修改设备采购信息")
def update_order_head(stc: StcOrdHeaderCreate, db: Session = Depends(get_db), token: str = Depends(oauth2_scheme)):
    try:
        user_id = EncryptionMiddleware.get_current_user(token)
        fitemcount = 0
        fpiececount = 0
        ftotalamt = 0
        fbillno = stc.fbillno
        fbillstate = stc.fbillstate
        with db.begin():
            if fbillstate == 8:
                sql = "update lims.s_tc_ord_head "
                sql = sql + " set fbillstate=8,fcheckdate=now(),fcheckuser= :userid "
                sql = sql + " where fbillno= :fbillno "
                db.execute(text(sql), {
                    "fbillno": fbillno,
                    "userid": user_id,
                })
            else:
                sql = "delete from lims.s_tc_ord_detail where fbillno= :fbillno "
                db.execute(text(sql), {"fbillno": fbillno})
                jsondata = stc.stcOrdDetails
                for data in jsondata:
                    sql = "insert into lims.s_tc_ord_detail(fbillno,fline,feuipmentid,fbuyprice,fqty,ftotamt,fmemo)"
                    sql = sql + " values( :fbillno , :fline , :feuipmentid , :fbuyprice , :fqty , :ftotamt , :fmemo )"
                    db.execute(text(sql), {
                        "fbillno": fbillno,
                        "fline": data.fline,
                        "feuipmentid": data.feuipmentid,
                        "fbuyprice": data.fbuyprice,
                        "fqty": data.fqty,
                        "ftotamt": data.ftotamt,
                        "fmemo": data.fmemo
                    })

                    fitemcount = fitemcount + 1
                    fpiececount = fpiececount + data.fqty
                    ftotalamt = ftotalamt + data.ftotamt
                sql = "update lims.s_tc_ord_head "
                sql = sql + " set forddate= :forddate ,forduser= :forduser ,fexplanation= :fexplanation ,fitemcount= :fitemcount ,fpiececount= :fpiececount ,ftotalamt= :ftotalamt ,fupddate=now(),foperator= :userid "
                sql = sql + " where fbillno= :fbillno "
                db.execute(text(sql), {
                    "forddate": stc.forddate,
                    "forduser": stc.forduser,
                    "fexplanation": stc.fexplanation,
                    "fitemcount": fitemcount,
                    "fpiececount": fpiececount,
                    "ftotalamt": ftotalamt,
                    "fbillno": fbillno,
                    "userid": user_id
                })
                return BaseResponse.success()
    except SQLAlchemyError as e:
        # 发生异常时自动回滚
        db.rollback()
        # 记录错误日志
        logging.error(f"数据库错误: {str(e)}")
        # 返回错误响应
        return BaseResponse.error(message="数据库操作失败")
    except Exception as e:
        db.rollback()
        logging.error(f"未知错误: {str(e)}")
        return BaseResponse.error(message="令牌过期")
